﻿using System.Data;
using System.Data.SqlClient;
using System.Net.Mail;
using System.Net;
using Pantry.Commons;
using Pantry.Entity;

namespace Pantry.DataAccess
{
    public class AccountDao
    {
        private static SqlConnection _sqlConnection;
        private const string QueryExecuteLogOn = @"SELECT COUNT(*) FROM Account WHERE UserName = @username AND Password = @password";
        private const string QueryExecuteChangePassword = @"SELECT COUNT(*) FROM Account WHERE UserName = @username AND Email = @email";
        private const string QueryExecuteGetRole = @"SELECT Role FROM Account 
                                                     WHERE UserName = @username
                                                     AND Password = @password";
        public AccountDao()
        {
            _sqlConnection = SqlConnectionTool.SqlGetConnection();
        }
        public AccountDao(string connectionString)
        {
            _sqlConnection  = new SqlConnection(connectionString);
        }

        /// <summary>
        /// Gets the role.
        /// </summary>
        /// <param name="user">The user.</param>
        /// <returns></returns>
        public int GetRole(User user)
        {
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = new SqlParameter("@username", SqlDbType.VarChar) { Value = user.UserName };
            parameters[1] = new SqlParameter("@password", SqlDbType.VarChar) { Value = user.Password };
            return (int)SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, QueryExecuteGetRole,parameters);
        }

        /// <summary>
        /// Logs the on.
        /// </summary>
        /// <param name="user">The user.</param>
        /// <returns></returns>
        public int LogOn(User user)
        {
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = new SqlParameter("@username", SqlDbType.VarChar) { Value = user.UserName };
            parameters[1] = new SqlParameter("@password", SqlDbType.VarChar) { Value = user.Password };
            return (int)SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, QueryExecuteLogOn, parameters);
        }

        /// <summary>
        /// Changes the password.
        /// </summary>
        /// <param name="user">The user.</param>
        /// <returns></returns>
        public int ChangePassword(User user)
        {
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = new SqlParameter("@username", SqlDbType.VarChar) { Value = user.UserName };
            parameters[1] = new SqlParameter("@email", SqlDbType.VarChar) { Value = user.Email };
            return (int)SqlHelper.ExecuteScalar(_sqlConnection, CommandType.Text, QueryExecuteChangePassword, parameters);
        }


        /// <summary>
        /// Sends the mail.
        /// </summary>
        /// <param name="toMail">To mail.</param>
        public void SendMail(string toMail)
        {
            // Gmail Address from where you send the mail
            const string fromMail = "pantry@gmail.com";
            //Password of your gmail address
            const string fromPassword = "pantry";
            // subject
            const string subject = "Mật khẩu mới";
            // content mail
            const string body = @"Mật khẩu mới của bạn là: bạn hãy vào đăng nhập tài khoản của bạn bằng mật khẩu mới này.";
            // smtp settings
            var smtp = new SmtpClient();
            {
                smtp.Host = "smtp.gmail.com";
                smtp.Port = 587;
                smtp.EnableSsl = true;
                smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
                smtp.Credentials = new NetworkCredential(fromMail, fromPassword);
                smtp.Timeout = 20000;
            }
            // Passing values to smtp object
            smtp.Send(fromMail, toMail, subject, body);
        }
        
    }
}
